OpenEdge Development:<br />ProDataSets Defining a static Data-Source

Before you can populate a ProDataSet from a database, you must define a Data-Source object for each of its member temp-table buffers. A Data-Source names either a database buffer that supplies fields for a ProDataSet temp-table or a query name, which in turn references one or more buffers as well as defines a specific set of retrieval criteria. If you simply name the buffer, then Progress determines the query when a FILL occurs by examining the Data-Relation between the ProDataSet member buffer for the Data-Source and its parent. If it has no parent and there is no query definition, then all records from the database table are retrieved. If the Data-Source involves a join between two or more database tables, the user-written query is required to describe the relationship between the database tables.

The Data-Source is defined independently of any ProDataSet, using this statement:


DEFINE DATA-SOURCE `dsource-name` FOR [QUERY `query-name` ] [ `source-buffer-phrase` [, `source-buffer-phrase` ] … ].

dsource-name is the name you are giving to the Data-Source, as you would for any other static object.

query-name is the name of a query you have defined separately, before the Data-Source definition.

Syntax
`buffer-name` [ KEYS ( { `field` [,`field`]… \| ROWID } ) ].

buffer-name is a buffer name for a database table.

field is a field name in that table.

ROWID can occur exactly once in place of a field list to use the ROWID of the database record as the key. If you do this, you must define a field in the ProDataSet temp-table to hold the value of the RowID and map the RowID to that field when you attach the Data-Source to the ProDataSet temp-table buffer.

Note that the QUERY phrase and the source-buffer-phrase are not mutually exclusive. If you specify a query, then the query definition itself names the buffer or buffers it uses. However, you might still want to include a source-buffer-phrase in the Data-Source definition in order to define the fields that make up the unique key Progress should use to eliminate duplicate records when it is filling the table from the Data-Source, or to locate the database records for a temp-table record when you have updated the record.

If you specify a source-buffer-phrase without the QUERY phrase and the Data-Source is for a child of a Data-Relation, then Progress can generate the correct query when you fill the ProDataSet to load children of each of the parent records. Otherwise, the default is to load all records from the database table. If there is no QUERY phrase, then you can have only one source-buffer-phrase if you intend to use the Data-Source to fill a ProDataSet. Progress cannot automatically join multiple tables without a query definition.

Note that if you use the Data-Source solely to write changes back to the database using the Data-Source definition, then there is no need for a query at all.

Attaching Data-Sources

After you have defined a ProDataSet and its Data-Sources, you use the ATTACH-DATA-SOURCE method to associate them. This method, which is described in more detail later in this chapter, lets you specify which fields from the Data-Source go into the ProDataSet, and whether any are renamed in the process. You’ll learn how to use this method a little later. In the meantime, there are a few rules about how you specify the key fields for a Data-Source.

All the fields in the KEYS phrase must be represented in the ProDataSet buffer that the Data-Source is attached to. That is, they must not be excluded from the table in an EXCEPT list as part of the ATTACH-DATA-SOURCE method that associated the buffer and the Data-Source. The fields might be renamed in the ProDataSet buffer, however, and mapped in the ATTACH-DATA-SOURCE method. Instead of a field list, the KEYS phrase can specify (also within parentheses) the single keyword ROWID, in which case the table RowID is used as the key for retrieval and updating. In this case, there must be a field in the ProDataSet buffer that is mapped to the table ROWID at the time of the ATTACH.

If you don’t specify the KEYS phrase, then Progress uses one or more primary keys of the database tables to determine the key fields. Therefore, if the primary key is in fact the appropriate key to use to do a unique FIND on a record, then you do not need to specify it in the definition. If the KEYS phrase is not specified and the database tables do not have a unique primary key, then Progress has no way to locate a record for update or delete or to eliminate duplicate records. This means that if records in the ProDataSet are deleted or updated, the developer must provide an event procedure for that event that handles the operation—there can be no default support provided by Progress.

Because the KEYS list is associated with a specific buffer, you must include the source-buffer-phrase in the definition if you need to specify the KEYS. If you also specify a QUERY name, then the buffer list itself is really redundant, except to identify which keys go with which buffer, since the query definition also specified them. In this case Progress simply verifies that the list of buffers is the same.

Example

In the example for ProDataSet dsOrder, these are the Data-Source definitions, along with their queries:

DEFINE QUERY qOrder FOR Order, Customer, SalesRep. DEFINE QUERY qItem FOR ITEM. DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder Order KEYS (OrderNum), Customer KEYS (CustNum), SalesRep KEYS (SalesRep). DEFINE DATA-SOURCE srcOline FOR OrderLine. DEFINE DATA-SOURCE srcItem FOR QUERY qItem ITEM KEYS (ItemNum).

DEFINE QUERY qOrder FOR Order, Customer, SalesRep. 
 DEFINE QUERY qItem  FOR ITEM. 
 DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder  
    Order KEYS (OrderNum), Customer KEYS (CustNum), SalesRep KEYS (SalesRep). 
 DEFINE DATA-SOURCE srcOline FOR OrderLine. 
 DEFINE DATA-SOURCE srcItem FOR QUERY qItem ITEM  KEYS (ItemNum).

You need a definition for query qOrder for two reasons. First, it is the top-level query, so unless you want all Orders and all their OrderLines and Items in the ProDataSet at the same time, which is unlikely (and which would be very expensive to load and pass between sessions), you will need to open that query with specific selection criteria for a single Order or a group of related orders. The second reason it’s needed is that it joins the Customer and SalesRep tables to the Order to pick up the Customer Name and SalesRep Name fields. You can’t do this join without the query because Progress won’t be able to join the tables for you automatically.

There is also a query qItem for the Item table so that under some circumstances you can specify that you want all Items loaded into the ProDataSet.

The Data-Source srcOrder shows you how to define the keys for each table in query qOrder. In this case, each of these is a unique primary key for the table, so the buffer list and the KEYS phrases are not strictly necessary. However, they provide you with explicit confirmation within your procedure of which keys are used to identify records.

Likewise, the phrase ITEM KEYS (ItemNum) is not strictly necessary in the Data-Source srcItem.

Data-Source as a separate object

Defining the Data-Source as a separate object allows you to define the ProDataSet without having to combine the Data-Source definitions with the ProDataSet definition. When a ProDataSet is passed to another session, the Data-Sources are not passed as part of the object, since they have no meaning on another session and their database tables cannot normally be referenced there. Separating the ProDataSet from its Data-Sources also allows a ProDataSet to attach and detach Data-Sources during program execution, and to switch Data-Sources when this is necessary, for example to retrieve data from different databases. You cannot pass a Data-Source as a parameter, but you can access the handle of an attached Data-Source through its ProDataSet if the ProDataSet is passed locally. You can also simply include the same Data-Source definition in multiple procedures where this is appropriate.

However, once the Data-Source is instantiated as an actual object instance, it can be attached to only one ProDataSet at a time.

Whether or not to define a query for a Data-Source

There are several ways in which you can use a Data-Source. How you are using it determines whether it is appropriate or necessary to associate it with an actual query or simply with one or more buffer names. In the following cases it is appropriate not to have a query for the Data-Source:

If the Data-Source is for a child table in a relation, then it could indicate that the default query generated by Progress on a FILL is sufficient, which retrieves all child records for the parent based on their foreign key relationship, so no query is needed.

If the Data-Source is for a top-level table (one with no parent) and you want to retrieve all records from the table named in its definition, then no query is needed. For a top-level table, retrieving all records is the default action on a FILL operation. This might be appropriate for something like the State table, if it is made a part of a ProDataSet in its entirety for lookup purposes.

If you are using the Data-Source for update purposes only, then no query is used or needed. The query is used only on a FILL. In this case the Data-Source is actually used as a “Target” for the updates.

If you intend to attach a query to the Data-Source dynamically at run time, then you do not need to associate it with a query in its definition. As with other Progress objects, ProDataSets let you combine static object definitions and dynamic actions on those objects very flexibly.

When you would not use a Data-Source at all

If you do not attach a Data-Source to a table before filling or updating the ProDataSet, then you must provide an event procedure to fill or update that table. Progress provides no default fill or update behavior in this case. If the source for the ProDataSet table is in fact one or more database tables, then you will normally want to define a Data-Source and attach it to the ProDataSet buffer to get the default behavior Progress provides. You can still extend that behavior in event procedures to further qualify which records go into the ProDataSet or to make other changes.

In some cases, however, your actual data source might not be a Progress or Data Server database at all. It could be a flat file that you read data from, an XML document that you process yourself, or some other source of data. In this case you simply do not define a Data-Source at all. Progress then depends on the event procedure code you write for the fill events for the table, and your own code must do the work of populating that table. If there’s no Data-Source for a temp-table and no supporting event procedure to replace it, then no data is loaded into that table, and no error results.

This gives you the flexibility to attach very different sources of data to the same ProDataSet without the ProDataSet definition or its business logic changing at all. At one time, the data might come from a file you read yourself. At another time, it might be moved into a database table. Or in a later release, Progress might natively support the nonstandard data source you use, and you can define a Data-Source object to handle it. You can do all this without changing anything at all in the ProDataSet itself.